//
//  FBDSqliteSentenceTools.m
//  EDCsfzs
//
//  Created by feng on 16/9/22.
//  Copyright © 2016年 牛高航. All rights reserved.
//
NSString* const Sqlite_PrimKey=@"INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL";
NSString* const Sqlite_IntKey=@"INT";
NSString* const Sqlite_TextKey=@"TEXT";
#import "FBDSqliteSentenceTools.h"

@implementation FBDSqliteSentenceTools

//生成Create Table表的语句
-(NSString*)fbd_sqliteCreateTable:(NSString*)tableName keyTypeArray:(NSMutableArray<NSArray*>*)mArray
{
    //@"create table emp(id int,ename varchar(30), age int,job varchar(20));"
    NSString* sql_Create=nil;
    __block NSString*commendStr=@"";
    if (mArray) {
        sql_Create=@"create table";
        
        NSArray<NSString*>*valueArray=[mArray firstObject];
        NSArray*typeArray=[mArray lastObject];
        [valueArray enumerateObjectsUsingBlock:^(NSString*  _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
          NSString* typeStr= [typeArray objectAtIndex:idx];
            commendStr=[NSString stringWithFormat:@"%@ %@ %@,",commendStr,obj,typeStr];
        }];
        
        commendStr=[commendStr substringToIndex:commendStr.length-1];
        commendStr=[NSString stringWithFormat:@"(%@)",commendStr];
    
    }
    
    sql_Create=[NSString stringWithFormat:@"%@ %@ %@",sql_Create,tableName,commendStr];
    
    
    return sql_Create;
}

//@"INSERT INTO  'emp' ('id','ename','age','job')  VALUES ('5','FENGyingxuan','1','ios teme leading')"
//生成Insert 语句
-(NSString*)fbd_sqliteInsertTable:(NSString*)tableName keyValueArray:(NSMutableArray<NSArray*>*)mArray
{
    NSString*sql_Insert=@"INSERT INTO";
    sql_Insert=[NSString stringWithFormat:@"%@ `%@`",sql_Insert,tableName];
    NSArray<NSString*>*keyArray=[mArray firstObject];
    NSArray<NSString*>*valueArray=[mArray lastObject];
    
    NSString*keyStr=nil;
    NSString*valueStr=@"";
  
    
    NSMutableArray* tmpeKeyArray=[NSMutableArray array];
    NSMutableArray* tmpeValueArray=[NSMutableArray array];
    
    [keyArray enumerateObjectsUsingBlock:^(NSString * _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
        obj=[NSString stringWithFormat:@"'%@'",obj];
        [tmpeKeyArray addObject:obj];
    }];
    [valueArray enumerateObjectsUsingBlock:^(NSString * _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
        obj=[NSString stringWithFormat:@"'%@'",obj];
        [tmpeValueArray addObject:obj];
    }];
    keyStr=[tmpeKeyArray componentsJoinedByString:@","];
    valueStr=[tmpeValueArray componentsJoinedByString:@","];
    sql_Insert=[NSString stringWithFormat:@"%@ (%@) VALUES (%@)",sql_Insert,keyStr,valueStr];
    NSLog(@">>>>>>>>>>%@",sql_Insert);
    
    
    
    

    return sql_Insert;
}
//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
-(NSString*)fbd_sqliteUpdateTable:(NSString*)tableName mNewValue:(NSString*)newV
                            cloum:(NSString*)cloum where:(NSString*)where
{
    if ((where&&[where hasPrefix:@"where"])||(where&&[where hasPrefix:@"WHERE"]))
    {
        where=[where substringFromIndex:5];
    }
    
    NSString*sql_Update=[NSString stringWithFormat:@"UPDATE '%@' SET %@='%@' WHERE %@",tableName,cloum,newV,where];
    return sql_Update;


}
//生成查询 语句
-(NSString*)fbd_sqliteSelectAllTable:(NSString*)tableName
{
    NSString*sql_all=[NSString stringWithFormat:@"select * from '%@'",tableName];
    return sql_all;

}
//查询某一个字段
-(NSString*)fbd_SearchCloumName:(NSString*)cloumName table:(NSString*)tableName
{
    //eg：SELECT FirstName FROM Persons
    NSString*searchSQL=nil;
    searchSQL=[NSString stringWithFormat:@"SELECT %@ FROM %@",cloumName,tableName];
    return searchSQL;
//    SELECT DISTINCT Company FROM Orders 其中关键字： DISTINCT代表去重的意思 把所有重复内容只保留一个

    
}
//查询某些字段
-(NSString*)fbd_SearchCloumNamesArray:(NSArray*)cloumNames table:(NSString*)tableName
{
    //eg： SELECT LastName,FirstName FROM Persons
    
    NSString*searchSQL=nil;
    searchSQL=[cloumNames componentsJoinedByString:@","];
    searchSQL=[NSString stringWithFormat:@"SELECT %@ FROM %@",searchSQL,tableName];
    return searchSQL;
}

//生成删除 语句
-(NSString*)fbd_sqliteDeleteAllTable:(NSString*)tableName
{
    NSString*sql_deleteAll=[NSString stringWithFormat:@"delete  from `%@`",tableName];
    
    return sql_deleteAll;

}
//生成删除table元素  附加条件
-(NSString*)fbd_sqliteDeleteTable:(NSString*)tableName where:(NSString*)where
{
    if ((where&&[where hasPrefix:@"where"])||(where&&[where hasPrefix:@"WHERE"]))
    {
        where=[where substringFromIndex:5];
    }
    NSString*sql_deleteWhere=[NSString stringWithFormat:@"delete  from `%@` where %@",tableName,where];
    return sql_deleteWhere;
}








// Like 模糊查询
// 通配符 %  -  [charlist] [^charlist] 或者[!charlist]
// IN 操作符允许我们在 WHERE 子句中规定多个值。
// Between 在什么之间的
// 通过使用 SQL，可以为列名称和表名称指定别名（Alias）
//E:SELECT column_name(s) FROM table_name AS alias_name
//E:SELECT column_name AS alias_name FROM table_name















/*
 -(void)testFMDB
 {
 FBDSqliteSentenceTools *tools=[[FBDSqliteSentenceTools alloc] init];
 
 self.databaseQueue = [TYDatebaseQueueFactory sharedDatabaseQueueWithPath:BD_VoicesDir withDatabaseName:@"BOOK_DB_NAME.database"];
 TYCommonDatabaseAccess *commend=[[TYCommonDatabaseAccess alloc] init];
 
 //create table
 NSArray*valueArrays=@[@"id",@"age",@"name",@"address",@"green"];
 NSArray*typeArrays= @[Sqlite_PrimKey,Sqlite_IntKey,Sqlite_TextKey,Sqlite_TextKey,Sqlite_TextKey];
 NSString*crateSQL=[tools fbd_sqliteCreateTable:@"DONGYE" keyTypeArray:[NSMutableArray arrayWithObjects:valueArrays,typeArrays, nil]  ];
 BOOL isCreateOk=[commend createTableWithSql:crateSQL inDatabaseQueue:self.databaseQueue];
 NSLog(@"数据库创建状态：%d",isCreateOk);
 
 //insert into
 NSArray*valueArray=@[@"age",@"name",@"address",@"green"];
 NSArray*typeArray=@[@"1",@"迎萱",@"上海市中山北路556#",@"幼儿园大班"];
 NSString*resultSql=[tools fbd_sqliteInsertTable:@"DONGYE" keyValueArray:[NSMutableArray arrayWithObjects:valueArray,typeArray, nil]];
 BOOL isInsertStatus=[commend executeUpdateWithSql:resultSql inDatabaseQueue:_databaseQueue];
 NSLog(@"数据库插入的状态：%d",isInsertStatus);
 
 // update
 NSString* updateSQL=[tools fbd_sqliteUpdateTable:@"DONGYE" mNewValue:@"110" cloum:@"age" where:@"id>8"];
 BOOL updateStatus=[commend  executeUpdateWithSql:updateSQL inDatabaseQueue:_databaseQueue];
 NSLog(@"update DataBase status is :%d",updateStatus);
 
 
 //search
 NSArray* searchArray=[commend executeQueryWithSql:[tools fbd_sqliteSelectAllTable:@"DONGYE"] inDatabaseQueue:_databaseQueue itemClass:NSClassFromString(@"MyRecorderModel") mappingBlock:^(TYMappingObject *mappingObject) {
 [mappingObject setColumnName:@"id" mappingToPorpertyName:@"m_id"];
 [mappingObject setColumnName:@"job" mappingToPorpertyName:@"m_file_name"];
 }];
 NSLog(@"searchArray :%@",searchArray);
 
 //delete
 NSString* deleteSQL= [tools fbd_sqliteDeleteTable:@"DONGYE" where:@"WHERE id>8"];
 NSLog(@"deleteSQL is %@",deleteSQL);
 BOOL deleteSQLStatus=[commend  executeUpdateWithSql:deleteSQL inDatabaseQueue:_databaseQueue];
 NSLog(@"deleteSQLStatus DataBase status is :%d",deleteSQLStatus);
 
 }
 */



@end
